'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    const { INTEGER, STRING, DECIMAL, DATE, TEXT, BOOLEAN, ENUM } = Sequelize;
    
    await queryInterface.createTable('bank_transactions', {
      id: {
        type: INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      transaction_id: {
        type: STRING(200),
        allowNull: false,
        unique: true,
        comment: '流水号'
      },
      payer_name: {
        type: STRING(200),
        allowNull: false,
        comment: '付款方名称'
      },
      payer_account: {
        type: STRING(100),
        comment: '付款方账号'
      },
      amount: {
        type: DECIMAL(12, 2),
        allowNull: false,
        comment: '交易金额'
      },
      transaction_date: {
        type: DATE,
        allowNull: false,
        comment: '交易日期'
      },
      transaction_time: {
        type: DATE,
        allowNull: false,
        comment: '交易时间'
      },
      balance: {
        type: DECIMAL(12, 2),
        comment: '交易后余额'
      },
      remark: {
        type: TEXT,
        comment: '交易备注'
      },
      data_source: {
        type: ENUM('file_import', 'plugin_capture'),
        defaultValue: 'file_import',
        comment: '数据来源'
      },
      import_batch: {
        type: STRING(100),
        comment: '导入批次号'
      },
      is_filtered: {
        type: BOOLEAN,
        defaultValue: false,
        comment: '是否已过滤'
      },
      filter_reason: {
        type: STRING(100),
        comment: '过滤原因'
      },
      created_at: {
        type: DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      }
    });

    // 添加索引
    await queryInterface.addIndex('bank_transactions', ['payer_name', 'transaction_date'], {
      name: 'bank_transactions_idx_payer_date',
      unique: false
    });
    
    await queryInterface.addIndex('bank_transactions', ['amount', 'transaction_date'], {
      name: 'bank_transactions_idx_amount_date',
      unique: false
    });
    
    await queryInterface.addIndex('bank_transactions', ['import_batch'], {
      name: 'bank_transactions_idx_import_batch',
      unique: false
    });
  },

  down: async (queryInterface, Sequelize) => {
    // 删除索引
    await queryInterface.removeIndex('bank_transactions', 'bank_transactions_idx_payer_date');
    await queryInterface.removeIndex('bank_transactions', 'bank_transactions_idx_amount_date');
    await queryInterface.removeIndex('bank_transactions', 'bank_transactions_idx_import_batch');
    
    await queryInterface.dropTable('bank_transactions');
  }
};